"""
封装数据库, 制作成一个工具类, 使用的时候, 只需要传入不同的sql语句, 即可实现数据库相关操作

create_conn: 创建连接的方法
query_one:  查一条记录
db_uid: 增删改记录
"""
import pymysql
class DBTools(object):
    # 创建连接 -->类方法可以直接使用类名调用
    @classmethod
    def creat_connect(cls):
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2021", database="ihrm",
                               charset="utf8")
        # 不能遗漏
        return conn
    # 查询一条记录 --> 封装为类方法,方便调用
    @classmethod
    def query_one(cls,sql):
        my_cursor = None
        my_conn = None
        res = None
        try:
            # 创建连接,借助类名,调用类方法 creat_connect
            my_conn = DBTools.creat_connect()
            # 创建游标
            my_cursor = my_conn.cursor()
            # 执行sql语句做查询
            my_cursor.execute(sql)
            res = my_cursor.fetchone()
            print(res)
        except Exception as e:
            print("执行查询SQL失败",e)
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()
            # 返回查询结果
            return res
    @classmethod
    def db_uid(cls,sql):
        my_cursor = None
        my_conn = None
        try:
            my_conn = DBTools.creat_connect()
            my_cursor = my_conn.cursor()
            res = my_cursor.execute(sql)
            print("增删改影响的行数",res)
            my_conn.commit()
        except Exception as e:
            print("增删改查失败",e)
            my_conn.rollback()
        finally:
            my_cursor.close()
            my_conn.close()
if __name__ == '__main__':
    DBTools.query_one("select * from t_book;")

    DBTools.db_uid("insert into t_book(id,title,pub_date) values(7777,'消失的第13级台阶','2022-05-23');")

    DBTools.db_uid("update t_book set `read` = `read` +100 where id = 7777;")

    DBTools.db_uid("delete from t_book where id = 7777;")



